--select * from [dbo].[CustomerNeedUpdateInfoSample]
SELECT * FROM TUANPA.[DQ_SME_360].DBO.RESULT_CHECKLIST 

select * from CustomerNeedUpdateInfoSample
delete from [CUSTOMERNEEDUPDATEINFOSAMPLE]

insert CustomerNeedUpdateInfoSample 
select [T24_CIF],[NAME],[ERROR_NAME],[T24_PHONE],[ERROR_T24_PHONE],[EMAIL],[ERROR_EMAIL],[ADDRESS],[ERROR_ADDRESS],[PROVINCE_CITY],[ERROR_PROVINCE_CITY],[VPB_INDUSTRY],[ERROR_VPB_INDUSTRY],[BUSINESS_REGISTRATION_NO],[ERROR_BUSINESS_REGISTRATION_NO],[INCORP_DATE],[ERROR_INCORP_DATE],[REVENUE],[ERROR_REVENUE],[TAX_CODE],[ERROR_TAX_CODE],[TAX_CODE_ISSUE_DATE],[ERROR_TAX_CODE_ISSUE_DATE],[LEGAL_REPRESENT_ID],[ERROR_LEGAL_REPRESENT_ID],[LEGAL_REPRESENT_NAME],[ERROR_LEGAL_REPRESENT_NAME],[LEGAL_REPRESENT_TITLE],[ERROR_LEGAL_REPRESENT_TITLE],[BOM_MEMBER_ID],[ERROR_BOM_MEMBER_ID],[BOM_MEMBER_NAME],[ERROR_BOM_MEMBER_NAME],[BOM_MEMBER_ADDRESS],[ERROR_BOM_MEMBER_ADDRESS],[INVESTOR_TYPE],[ERROR_INVESTOR_TYPE] from TUANPA.[DQ_SME_360].DBO.RESULT_CHECKLIST 

--DROP TABLE TBL_CHECK_CUSTOMER_INFO_UNPIVOT

delete from TBL_CHECK_CUSTOMER_INFO_UNPIVOT

Insert TBL_CHECK_CUSTOMER_INFO_UNPIVOT
SELECT LTRIM(str(A.T24_CIF)), CODE.INFO_CODE, X.VALUE_INFO, isnull(X.VALUE_CHECK_INFO,'Y')
FROM 
(SELECT INFO_CODE FROM TBL_CUSTOMER_INFO_CODE) CODE
CROSS JOIN (SELECT T24_CIF FROM [CUSTOMERNEEDUPDATEINFOSAMPLE]) AS A
LEFT JOIN
(SELECT * FROM (SELECT  * FROM [CUSTOMERNEEDUPDATEINFOSAMPLE]) P
		UNPIVOT
		(VALUE_INFO FOR NAME_INFO IN ([NAME],[T24_PHONE],[EMAIL],[ADDRESS],[PROVINCE_CITY],[INDUSTRY],[BUSINESS_REGISTRATION_NO],[INCORP_DATE],[REVENUE],[TAX_CODE],[TAX_CODE_ISSUE_DATE],[LEGAL_REPRESENT_ID],[LEGAL_REPRESENT_NAME],[LEGAL_REPRESENT_TITLE],[BOM_MEMBER_ID],[BOM_MEMBER_NAME],[BOM_MEMBER_ADDRESS],[INVESTOR_TYPE]
		)) AS UNPVT
		UNPIVOT
		(VALUE_CHECK_INFO FOR NAME_CHECK_INFO IN 
		([ERROR_NAME],[ERROR_T24_PHONE],[ERROR_EMAIL],[ERROR_ADDRESS],[ERROR_PROVINCE_CITY],[ERROR_INDUSTRY],[ERROR_BUSINESS_REGISTRATION_NO],[ERROR_INCORP_DATE],[ERROR_REVENUE],[ERROR_TAX_CODE],[ERROR_TAX_CODE_ISSUE_DATE],[ERROR_LEGAL_REPRESENT_ID],[ERROR_LEGAL_REPRESENT_NAME],[ERROR_LEGAL_REPRESENT_TITLE],[ERROR_BOM_MEMBER_ID],[ERROR_BOM_MEMBER_NAME],[ERROR_BOM_MEMBER_ADDRESS],[ERROR_INVESTOR_TYPE])) UNPVT1
WHERE NAME_INFO=SUBSTRING(NAME_CHECK_INFO,7,LEN(NAME_CHECK_INFO))
)X
ON CODE.INFO_CODE = X.NAME_INFO AND convert(nvarchar(50),A.T24_CIF) = convert(nvarchar(50),X.T24_CIF)
where VALUE_INFO is null or VALUE_INFO ='Y' ;

select * from TBL_CHECK_CUSTOMER_INFO_UNPIVOT

--update TBL_CHECK_CUSTOMER_INFO_UNPIVOT 
--set VALUE_CHECK_INFO = 'Y' where VALUE_INFO is null
	  
	  --DROP TABLE TBL_CUSTOMER_INFO_CODE

	  --SELECT DISTINCT NAME_INFO AS INFO_CODE INTO TBL_CUSTOMER_INFO_CODE FROM TBL_CHECK_CUSTOMER_INFO_UNPIVOT

	  --ALTER TABLE TBL_CUSTOMER_INFO_CODE ADD INFO_NAME NVARCHAR(MAX)
	  --drop table TBL_CUSTOMER_NEED_UPDATE_INFO
	  select * from TBL_CUSTOMER_INFO_CODE
	  delete from TBL_CUSTOMER_NEED_UPDATE_INFO

	  insert TBL_CUSTOMER_NEED_UPDATE_INFO
	  SELECT B.BUSINESS_DATE, B.CIF, B.CUS_NAME, B.BRANCH_ID, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, b.CUS_OPEN_DATE, B.DAO, B.DAO_NAME, A.NO_INFO_NEED_UPDATE 
	  FROM
	  (SELECT T24_CIF as T24_CIF, COUNT(INFO_CODE) AS NO_INFO_NEED_UPDATE FROM TBL_CHECK_CUSTOMER_INFO_UNPIVOT 
	  WHERE VALUE_CHECK_INFO = 'Y' 
	  --and T24_CIF = '3417403'
	  GROUP BY T24_CIF) A
	  LEFT JOIN TBL_CUSTOMER B ON A.T24_CIF = B.CIF

	  select * from CustomerNeedUpdateInfoSample where T24_CIF = '3417403'

	  select * from TBL_CHECK_CUSTOMER_INFO_UNPIVOT where T24_CIF ='3417403'

	  --update TBL_CHECK_CUSTOMER_INFO_UNPIVOT set t24_cif = '2413955' where t24_cif = '6789412'
	  select * from TBL_CUSTOMER_INFO_CODE a
	  left join  TBL_CHECK_CUSTOMER_INFO_UNPIVOT b on a.INFO_CODE = b.name_info